#!/usr/bin/env bash
dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -ureadonly -preadonly -h192.168.0.50<<EOF
use jiayou;
select y.month,y.send_tactics_id,y.tactics_title,y.count,ifnull(z.count,0)
from
(select  A.send_tactics_id,B.tactics_title,count(A.send_tactics_id)as count
from coupon_info as A
 join coupon_send_tactics as B
ON B.id = A.send_tactics_id and A.send_tactics_id is not null and A.coupon_statue = 3
group by A.send_tactics_id,B.tactics_title)z
right join
(select substr(B.release_time_start,1,10)as month, A.send_tactics_id,B.tactics_title,count(A.send_tactics_id) as count
from coupon_info as A
 join coupon_send_tactics as B
ON B.id = A.send_tactics_id and A.send_tactics_id is not null
group by A.send_tactics_id,B.tactics_title,substr(B.release_time_start,1,10)) y
on z.send_tactics_id = y.send_tactics_id;
EOF